package com.mh.jdbc.util;

import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.mh.jdbc.entity.MBean;
import com.mh.jdbc.enums.LikeType;
import com.mh.jdbc.enums.OType;
import com.mh.jdbc.exception.DAOException;

public class SqlUtils {
	/**
	 * 属性转表字段
	 * @param name
	 * @return
	 */
	static final String B2T(String name){
		StringBuffer sb = new StringBuffer();
		char tmp = 0;
		for(int i = 0;i<name.length();i++){
			tmp = name.charAt(i);
			if(tmp < 91 && tmp > 64){
				sb.append(i == 0 ? "" : "_").append((char)(tmp+32));
			}else{
				sb.append(tmp);
			}
		}
		return sb.toString();
	}
	/**
	 * 表字段转属性
	 * @param name
	 * @return
	 */
	static final String T2B(String name){
		StringBuffer sb = new StringBuffer(name);
		char tmp = 0;
		for(int i = 0;i<name.length();i++){
			tmp = name.charAt(i);
			if('_' == tmp){
				sb.delete(i, i+2).insert(i,(char)(name.charAt(i+1)-32));
			}
		}
		return sb.toString();
	}
	/**
	 * 集合转字符串，用于条件
	 * @param map
	 * @return
	 */
	private static final String toStr(Map<String,?> map){
		StringBuffer sb = new StringBuffer();
		Iterator<String> it = map.keySet().iterator();
		String key = null;
		Object obj = null;
		while(it.hasNext()){
			key = it.next().toString();
			obj = map.get(key);
			if(obj instanceof String){
				sb.append(" and ").append(B2T(key)).append("=").append("'").append(obj.toString()).append("'");
			}else{
				sb.append(" and ").append(B2T(key)).append("=").append(obj.toString());
			}
		}
		return sb.delete(0, 4).insert(0, " where").toString();
	}
	/**
	 * 插入拼装
	 * @param sb
	 * @param type
	 * @param val
	 * @return
	 */
	private static final StringBuilder insertStr(StringBuilder sb,String type,String val){
		if(type.contains("String") || type.contains("Date")){
			return sb.append(",").append("'").append(val).append("'");
		}else{
			return sb.append(",").append(val);
		}
	}
	/**
	 * 修改拼装
	 * @param sb
	 * @param name
	 * @param type
	 * @param val
	 * @return
	 */
	private static final StringBuilder updateStr(StringBuilder sb,String name,String type,String val){
		if(null != val && !"".equals(val.trim())){
			sb.append(",");
			if(type.contains("String") || type.contains("Date")){
				sb.append(B2T(name)).append('=').append("'").append(val).append("'");
			}else{
				sb.append(B2T(name)).append('=').append(val);
			}
		}
		return sb;
	}
	/**
	 * 条件拼装
	 * @param sb
	 * @param name
	 * @param type
	 * @param val
	 * @return
	 */
	private static final StringBuilder whereStr(StringBuilder sb,String name,String type,String val){
		if(null != val && !"".equals(val.trim())){
			sb.append(" and ");
			if(type.contains("String") || type.contains("Date")){
				sb.append(B2T(name)).append(" = ").append("'").append(val).append("'");
			}else{
				sb.append(B2T(name)).append(" = ").append(val);
			}
		}
		return sb;
	}
	/**
	 * 内容转换
	 * @param object
	 * @param type
	 * @return
	 * @throws DAOException
	 */
	public static final String convert(MBean mBean,int type) throws DAOException {
		StringBuilder sb = null;
		try {
			Class<? extends Object> clazz = mBean.getClass();
			Field[] fields = clazz.getDeclaredFields();
			sb = new StringBuilder();
			for (Field f : fields) {
				f.setAccessible(true);
				String fType = f.getType().toString();
				if (f.get(mBean) != null) {
					String val = f.get(mBean).toString();
					if(type == OType.INSERT){
						sb = insertStr(sb, fType, val);
					}else if(type == OType.UPDATE){
						sb = updateStr(sb, f.getName(), fType, val);
					}else if(type == OType.WHERE){
						sb = whereStr(sb, f.getName(), fType, val);
					}else{
						throw new DAOException("不存在该类型操作");
					}
				}else{
					if(type == OType.INSERT)
					sb = insertStr(sb, fType, null);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new DAOException(e);
		}
		if(type == OType.INSERT){
			sb.deleteCharAt(0).insert(0, '(').append(')');
		}else if(type == OType.UPDATE){
			sb.deleteCharAt(0).insert(0, " set ");
		}else if(type == OType.WHERE){
			if(!"".equals(sb.toString()))
			sb.delete(0, 5).insert(0, " where ");
		}else{
			throw new DAOException("不存在该类型操作");
		}
		return sb.toString();
	}
	/**
	 * 条件转换
	 * @param object
	 * @param type
	 * @return
	 */
	public static final String convertLike(MBean mBean,String likePro,int likeType) throws DAOException{
		StringBuilder sb = null;
		try {
			Class<? extends Object> clazz = mBean.getClass();
			Field[] fields = clazz.getDeclaredFields();
			sb = new StringBuilder();
			for (Field f : fields) {
				f.setAccessible(true);
				if (f.get(mBean) != null) {
					String fType = f.getType().toString();
					String val = f.get(mBean).toString();
					if(likePro.equals(f.getName())){
						sb.append(" and ");
						sb.append(B2T(likePro)).append(" like ").append("'").append(LikeType.Like(val, likeType)).append("'");
					}else{
						sb = whereStr(sb, f.getName(), fType, val);
					}
				}
			}
		} catch (Exception e) {
			throw new DAOException(e);
		}
		if("".equals(sb.toString())) return "";
		return sb.delete(0, 5).insert(0, " where ").toString();
	}
	/**
	 * 获取列
	 * @param obj
	 * @return
	 */
	public static final String[] getColNames(MBean mBean){
		Field[] fds = mBean.getClass().getDeclaredFields();
		int len = fds.length;
		String[] colNames = new String[len];
		for(int i = 0;i < len;i ++) colNames[i] = fds[i].getName();
		return colNames;
	}
	/**
	 * 获取列
	 * @param obj
	 * @return
	 */
	public static String getLimit(MBean mBean){
		StringBuilder sb = new StringBuilder();
		Class<?> supClas = mBean.getClass().getSuperclass();
		try {
			int startRow = Integer.parseInt(supClas.getDeclaredMethod("getStartRow").invoke(mBean).toString());
			int size = Integer.parseInt(supClas.getDeclaredMethod("getSize").invoke(mBean).toString());
			if(size != 0) sb.append(" limit ").append(startRow).append(",").append(size);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return sb.toString();
	}
	/**
	 * 插入
	 * @param obj
	 * @return
	 */
	public static String insertSQL(MBean mBean){
		StringBuilder sql = null;
		try {
			String clzName = mBean.getClass().getSimpleName();
			sql = new StringBuilder().append("insert into ").append(B2T(clzName)).append(" values").append(convert(mBean,OType.INSERT)).append(";");
		} catch (DAOException e) {
			e.printStackTrace();
		}
		return sql.toString();
	}
	/**
	 * 批量插入
	 * @param objs
	 * @return
	 */
	public static String insertBatchSQL(List<MBean> mBeans){
		StringBuilder sql = null;
		try {
			String clzName = mBeans.get(0).getClass().getSimpleName();
			sql = new StringBuilder().append("insert into ").append(B2T(clzName)).append(" values");
			StringBuilder tmp = new StringBuilder();
			for(MBean mBean : mBeans){
				tmp.append(",").append(convert(mBean,OType.INSERT));
			}
			sql.append(tmp.deleteCharAt(0)).append(";");
		} catch (DAOException e) {
			e.printStackTrace();
		}
		return sql.toString();
	}
	/**
	 * 单个删除
	 * @param objs
	 * @return
	 * @throws DAOException
	 */
	public static String deleteSQL(MBean mBean){
		StringBuilder sql = null;
		try {
			String clzName = mBean.getClass().getSimpleName();
			sql = new StringBuilder().append("delete from ").append(B2T(clzName)).append(" ").append(convert(mBean,OType.WHERE));
		} catch (DAOException e) {
			e.printStackTrace();
		}
		return sql.toString();
	}
	/**
	 * 自定义条件
	 * @param obj
	 * @return
	 */
	public static String deleteSQL(MBean mBean,String condite){
		StringBuilder sql = null;
		String clzName = mBean.getClass().getSimpleName();
		sql = new StringBuilder().append("delete from ").append(B2T(clzName)).append(" where ").append(condite).append(";");
		return sql.toString();
	}
	/**
	 * 批量删除条件
	 * @param objs
	 * @return
	 * @throws DAOException
	 */
	public static String deleteBatchByIdsSQL(List<?> ids,MBean mBean,String idName){
		StringBuilder sql = null;
		sql = new StringBuilder();
		String clzName = mBean.getClass().getSimpleName();
		sql = new StringBuilder().append("delete from ").append(B2T(clzName)).append(" ");
		StringBuilder tmp = new StringBuilder();
		for(Object id : ids){
			if(id instanceof String){
				tmp.append(",").append("'").append(id).append("'");
			}else{
				tmp.append(",").append(id);
			}
		}
		sql.append("where ").append(B2T(idName)).append(" in (").append(tmp.deleteCharAt(0).append(");"));
		return sql.toString();
	}
	/**
	 * 单条件修改方法
	 * @param obj
	 * @param condit
	 * @return
	 */
	public static String updateSQL(MBean mBean,String idName){
		StringBuilder sql = null;
		try {
			sql = new StringBuilder();
			StringBuilder tmp = new StringBuilder();
			String clzName = mBean.getClass().getSimpleName();
			Field f = mBean.getClass().getDeclaredField(idName);
			f.setAccessible(true);
			Object val = f.get(mBean);
			tmp = !(val instanceof String) ? tmp.append(val) : tmp.append("'").append(val).append("'");
			sql.append("update ").append(B2T(clzName)).append(" ").append(convert(mBean,OType.UPDATE)).append(" where ").append(B2T(idName)).append("=").append(tmp).append(";");
		} catch (DAOException e) {
			e.printStackTrace();
		}catch (Exception e) {
			e.printStackTrace();
		}
		return sql.toString();
	}
	/**
	 * 多条件修改方法
	 * @param obj
	 * @param idName
	 * @return
	 */
	public static String updateSQL(MBean mBean,Map<String,?> condit){
		StringBuilder sql = null;
		try {
			sql = new StringBuilder();
			sql.append("update ").append(B2T(mBean.getClass().getSimpleName())).append(convert(mBean,OType.UPDATE)).append(toStr(condit));
		} catch (DAOException e) {
			e.printStackTrace();
		}catch (Exception e) {
			e.printStackTrace();
		}
		return sql.toString();
	}
	/**
	 * 查询某一字段
	 * @param obj
	 * @return
	 */
	public static String querySQL(MBean mBean,String tergetPro){
		StringBuilder sql = new StringBuilder();
		try {
			sql.append("select ").append(B2T(tergetPro)).append(" from ").append(B2T(mBean.getClass().getSimpleName())).append(convert(mBean, OType.WHERE));
		} catch (DAOException e) {
			e.printStackTrace();
		}
		return sql.toString();
	}
	/**
	 * 全量查询
	 * @param obj
	 * @return
	 */
	public static String querySQL(MBean mBean){
		StringBuilder sql = new StringBuilder();
		try {
			sql.append("select * from ").append(B2T(mBean.getClass().getSimpleName())).append(convert(mBean, OType.WHERE)).append(getLimit(mBean));
		} catch (DAOException e) {
			e.printStackTrace();
		}
		return sql.toString();
	}
	/**
	 * 模糊查询
	 * @param obj
	 * @return
	 */
	public static String querySQL(MBean mBean,String likePro,int likeType){
		StringBuilder sql = new StringBuilder();
		try {
			sql.append("select * from ").append(B2T(mBean.getClass().getSimpleName())).append(convertLike(mBean, likePro,likeType)).append(getLimit(mBean));
		} catch (DAOException e) {
			e.printStackTrace();
		}
		return sql.toString();
	}
	/**
	 * 查询数量
	 * @param obj
	 * @return
	 */
	public static String getCountSQL(MBean mBean){
		StringBuilder sql = new StringBuilder();
		try {
			sql.append("select count(0) rows from ").append(B2T(mBean.getClass().getSimpleName())).append(convert(mBean, OType.WHERE));
		} catch (DAOException e) {
			e.printStackTrace();
		}
		return sql.toString();
	}
	/**
	 * 查询数量,模糊条件
	 * @param obj
	 * @return
	 */
	public static String getCountSQL(MBean mBean,String likePro,int likeType){
		StringBuilder sql = new StringBuilder();
		try {
			sql.append("select count(0) rows from ").append(B2T(mBean.getClass().getSimpleName())).append(convertLike(mBean, likePro,likeType));
		} catch (DAOException e) {
			e.printStackTrace();
		}
		return sql.toString();
	}
	/**
	 * 唯一性校验SQL
	 * @param ClassName
	 * @param key
	 * @param val
	 * @return
	 */
	public static String getCountSQL(String className,String key,Object val){
		StringBuilder sql = new StringBuilder();
		sql.append("select count(0) rows from ").append(B2T(className)).append(" where ");
		if(val instanceof String || val instanceof Date ){
			sql.append(B2T(key)).append(" = ").append("'").append(val).append("'");
		}else{
			sql.append(B2T(key)).append(" = ").append(val);
		}
		return sql.toString();
	}
	/**
	 * 唯一性校验SQL
	 * @param MBean
	 * @param proName
	 * @return
	 */
	public static String getCountSQL(MBean mBean,String proName){
		Class<? extends MBean> clz = mBean.getClass();
		String className = clz.getSimpleName();
		try {
			for(Field field : clz.getDeclaredFields()){
				field.setAccessible(true);
				String name = field.getName();
				if(proName.equals(name)){
					Object val = field.get(mBean);
					if(null != val) return getCountSQL(className,proName,val);
					break;
				}
			}
			return "0000";
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	/**
	 * 添加参数
	 * @param ps
	 * @param objs
	 * @throws SQLException
	 */
	public static void setAttribute(PreparedStatement ps,Object...objs) throws DAOException{
		try {
			if(objs != null) for(int i=0;i<objs.length;i++) ps.setObject(i+1, objs[i]);
		} catch (SQLException e) {
			throw new DAOException(e);
		}
	}
}
